package com.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.dao.TrailDAO;
import com.excel.ExcelUtil;
import com.pojo.Dictionary;
import com.pojo.Qiye;
import com.pojo.Trail;
import com.pojo.Wuye;
import com.util.ValidateHelper;
import com.vo.TrailVo;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class TrailService extends ServiceImpl<TrailDAO, Trail> {

    @Autowired
    private TrailDAO trailDAO;

    @Autowired
    private DictionaryService dictionaryService;

    @Autowired
    private QiyeService qiyeService;

    @Autowired
    private WuyeService wuyeService;

    /**
     * 轨迹列表
     * @param businessName
     * @param staffName
     * @param time1
     * @param time2
     * @param page
     * @param limit
     * @param queryStr
     * @return
     */
    public IPage<TrailVo> getTrailList(String businessName, String staffName, String time1, String time2, Integer page, Integer limit, String queryStr) {
        if (ValidateHelper.isEmptyString(businessName)) {
            businessName = null;
        }
        if (ValidateHelper.isEmptyString(staffName)) {
            staffName = null;
        }
        if (ValidateHelper.isEmptyString(time1)) {
            time1 = null;
        }
        if (ValidateHelper.isEmptyString(time2)) {
            time2 = null;
        }
        Page p = new Page(page, limit);

        List<String> zyNameList = new ArrayList<>();
        if (ValidateHelper.isNotEmptyString(queryStr)) {
            List<Dictionary> zyList = new ArrayList<>();
            if ("gs".equals(queryStr)) {
                zyList = dictionaryService.getDicByZyGs();
            }else if ("jt".equals(queryStr)) {
                zyList = dictionaryService.getDicByZyJt();
            }
            if (ValidateHelper.isNotEmptyCollection(zyList)) {
                for (Dictionary dictionary : zyList) {
                    if (ValidateHelper.isNotEmptyString(dictionary.getName())) {
                        zyNameList.add(dictionary.getName());
                    }
                }
            }
        }

        IPage<TrailVo> iPage = trailDAO.selectTrail(p, businessName, staffName, time1, time2, queryStr, zyNameList);
        return iPage;
    }

    /**
     * 轨迹列表-导出Excel
     * @param businessName
     * @param staffName
     * @param time1
     * @param time2
     * @param queryStr
     * @return
     */
    public List<TrailVo> exportTrailList(String businessName, String staffName, String time1, String time2, String queryStr) {
        if (ValidateHelper.isEmptyString(businessName)) {
            businessName = null;
        }
        if (ValidateHelper.isEmptyString(staffName)) {
            staffName = null;
        }
        if (ValidateHelper.isEmptyString(time1)) {
            time1 = null;
        }
        if (ValidateHelper.isEmptyString(time2)) {
            time2 = null;
        }

        List<String> zyNameList = new ArrayList<>();
        if (ValidateHelper.isNotEmptyString(queryStr)) {
            List<Dictionary> zyList = new ArrayList<>();
            if ("gs".equals(queryStr)) {
                zyList = dictionaryService.getDicByZyGs();
            }else if ("jt".equals(queryStr)) {
                zyList = dictionaryService.getDicByZyJt();
            }
            if (ValidateHelper.isNotEmptyCollection(zyList)) {
                for (Dictionary dictionary : zyList) {
                    if (ValidateHelper.isNotEmptyString(dictionary.getName())) {
                        zyNameList.add(dictionary.getName());
                    }
                }
            }
        }

        List<TrailVo> iPage = trailDAO.exportTrailList(businessName, staffName, time1, time2, queryStr, zyNameList);
        return iPage;
    }

    /**
     * 根据员工查询轨迹
     * @param staffName
     * @param time1
     * @return
     */
    public List<TrailVo> getTrailListByStaff(String staffName, String time1) {
        if (ValidateHelper.isEmptyString(staffName)) {
            staffName = null;
        }
        if (ValidateHelper.isEmptyString(time1)) {
            time1 = null;
        }

        List<TrailVo> trailList = trailDAO.selectTrailList(staffName, time1);
        return trailList;
    }

    /**
     * 根据商户查询轨迹
     * @param businessBh
     * @param time1
     * @param page
     * @param limit
     * @return
     */
    public IPage<TrailVo> getTrailListByBusiness(Integer businessBh, String time1, Integer page, Integer limit) {
        Page p = new Page(page, limit);
        IPage<TrailVo> trailList = trailDAO.selectTrailListByBusiness(p, businessBh, time1);
        return trailList;
    }

    /**
     * 清理30天之前的轨迹
     * @return
     */
    public int batchDeleteTrail(String endTime){

//        List<Trail> list = trailDAO.selectList(new QueryWrapper<Trail>().le("rd_time" , endTime)) ;
//        List<String> ids = new ArrayList<>();
//        int count = 0;
//        if (ValidateHelper.isNotEmptyCollection(list)) {
//            for (Trail trail : list) {
//                ids.add(trail.getId());
//            }
//            count = trailDAO.deleteBatchIds(ids);
//        }
//        return count ;
        //2021.12.16 修改为直接删除
        QueryWrapper q = new QueryWrapper();
        q.le("rd_time",endTime);
        int result = trailDAO.delete(q);
        return result;


    }

    /**
     * 查询轨迹列表
     * @param staffBh
     * @param businessBh
     * @return
     */
    public List<Trail> getTrail(String staffBh, Integer businessBh) {
        QueryWrapper<Trail> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("staff_bh", staffBh);
        queryWrapper.eq("busi_bh", businessBh);
        queryWrapper.orderByDesc("rd_time");
        List<Trail> trailList = trailDAO.selectList(queryWrapper);
        return trailList;
    }

    /**
     * 根据员工编号查询轨迹列表
     * @param staffBh
     * @return
     */
    public List<Trail> getTrailByStaffBh(String staffBh) {
        QueryWrapper<Trail> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("staff_bh", staffBh);
        queryWrapper.orderByDesc("rd_time");
        List<Trail> trailList = trailDAO.selectList(queryWrapper);
        return trailList;
    }


    /**
     * 商户访问记录详情
     * @param businessBh
     * @param queryDate
     * @param page
     * @param limit
     * @return
     * @throws ParseException
     */
    public IPage<TrailVo> getBusinessStatisticsDetail(String businessBh, String queryDate, Integer page, Integer limit) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = sdf.parse(queryDate);
        String dateStr = sdf.format(date);
        Page p = new Page(page, limit);
        IPage<TrailVo> iPage = trailDAO.getBusinessStatisticsDetail(p, businessBh, dateStr);
        return iPage;
    }

    /**
     * 商户访问记录详情
     * @param businessBh
     * @param queryDate
     * @return
     * @throws ParseException
     */
    public List<TrailVo> getBusinessStatisticsDetailByExport(String businessBh, String queryDate) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = sdf.parse(queryDate);
        String dateStr = sdf.format(date);
        List<TrailVo> iPage = trailDAO.getBusinessStatisticsDetailByExport(businessBh, dateStr);
        return iPage;
    }

    /**
     * 员工访问记录详情
     * @param staffBh
     * @param queryDate
     * @param page
     * @param limit
     * @return
     * @throws ParseException
     */
    public IPage<TrailVo> getStaffStatisticsDetail(String staffBh, String queryDate, Integer page, Integer limit) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = sdf.parse(queryDate);
        String dateStr = sdf.format(date);
        Page p = new Page(page, limit);
        IPage<TrailVo> iPage = trailDAO.getStaffStatisticsDetail(p, staffBh, dateStr);
        return iPage;
    }

    /**
     * 员工轨迹
     * @param staffBh
     * @return
     */
    public Trail getStaffTrail(String staffBh) {
        QueryWrapper q = new QueryWrapper();
        q.eq("staff_bh", staffBh);
        q.orderByDesc("rd_time");
        q.last("limit 1");
        Trail trail = trailDAO.selectOne(q);
        return trail;
    }

    /**
     * 员工轨迹记录导出Excel
     * @param fileName
     * @param sheetName
     * @param resultList
     * @throws Exception
     */
    public void exportByTrail(String fileName, String sheetName, List resultList) throws Exception {
        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
        //设置title
        List<String> titles = new ArrayList();
        titles.add("序号");
        titles.add("员工编号");
        titles.add("员工姓名");
        titles.add("员工所属公司");
        titles.add("员工所属物业");
        titles.add("职业");
        titles.add("商户名称");
        titles.add("商户编号");
        titles.add("地址");
        titles.add("商户所属物业");
        titles.add("入店时间");
        titles.add("离店时间");

        Label label=null;

        int iMaxLines = 60000;
        int totalLines = resultList.size();
        int page = totalLines / iMaxLines;
        int lastLines = totalLines % iMaxLines;//尾页要写的行数
        if (totalLines == 0) {
            return;
        }
        if (page >= 1 && lastLines > 0) {
            page = page + 1;
        }
        if(page == 0){
            page = 1;
        }

        for(int p = 0; p < page; p ++){
            WritableSheet sheet = workbook.createSheet(sheetName + (p + 1), p);
            int iStartRow = 0;
            int iEndRow = 0;
            if (p == 0) {
                iStartRow = 0;
                if (page == 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }
            } else {
                iStartRow = p * iMaxLines;
                if (p == page - 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }
            }
            List listPageValue = new ArrayList();//保存每页的数据
            for (int i = iStartRow; i <= iEndRow; i++) {
                if (resultList.get(i) == null) {
                    continue;
                }
                listPageValue.add(resultList.get(i));
            }

            WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
            titleStyle.setBackground(Colour.SEA_GREEN);
            titleStyle.setAlignment(Alignment.CENTRE);
            titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
            titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
            for(int i = 0; i < titles.size(); i ++){
                //x,y,第一行的列名
                label=new Label(i,0,titles.get(i),titleStyle);
                //7：添加单元格
                sheet.addCell(label);
            }

            WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        title.setBackground(Colour.VERY_LIGHT_YELLOW);
            colmStyle.setAlignment(Alignment.CENTRE);
            colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
            colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
            for(int i = 0; i < listPageValue.size(); i ++){
                TrailVo trailVo = (TrailVo) listPageValue.get(i);
                label=new Label(0,i + 1, String.valueOf(i + 1),colmStyle);
                sheet.addCell(label);
                label=new Label(1,i + 1, trailVo.getStaffBh(), colmStyle);
                sheet.addCell(label);
                label=new Label(2,i + 1, trailVo.getStaffName(), colmStyle);
                sheet.addCell(label);
                Qiye qiye = qiyeService.getById(trailVo.getQyId());
                String qyName = "";
                String wyName = "";
                if (null != qiye) {
                    qyName = qiye.getQyname();
                    Wuye wuye = wuyeService.getById(qiye.getWyID());
                    if (null != wuye) {
                        wyName = wuye.getWyname();
                    }
                }
                label=new Label(3,i + 1, qyName, colmStyle);
                sheet.addCell(label);
                label=new Label(4,i + 1, wyName, colmStyle);
                sheet.addCell(label);
                label=new Label(5,i + 1, trailVo.getStaffZy(), colmStyle);
                sheet.addCell(label);
                label=new Label(6,i + 1, trailVo.getBusiName(), colmStyle);
                sheet.addCell(label);
                label=new Label(7,i + 1, trailVo.getBusiBh(), colmStyle);
                sheet.addCell(label);
                label=new Label(8,i + 1, trailVo.getAddress(), colmStyle);
                sheet.addCell(label);
                label=new Label(9,i + 1, trailVo.getWyName(), colmStyle);
                sheet.addCell(label);
                label=new Label(10,i + 1, sdf.format(trailVo.getRdTime()), colmStyle);
                sheet.addCell(label);
                String cdTime = "";
                if (null != trailVo.getCdTime()) {
                    cdTime = sdf.format(trailVo.getCdTime());
                }
                label=new Label(11,i + 1,  cdTime, colmStyle);
                sheet.addCell(label);
            }
            ExcelUtil.setColumnAutoSize(sheet,10);
        }


        workbook.write();
        workbook.close();
    }

}
